-- Visual representation: https://dbdiagram.io/d/62a7ce3b9921fe2a96f9e2cb

CREATE OR REPLACE FUNCTION many_to_many_duplicate() RETURNS TRIGGER AS
$many_to_many_duplicate$
BEGIN
    INSERT INTO vulnerability.equivalent (a, b) VALUES (NEW.b, NEW.a) ON CONFLICT DO NOTHING;
    RETURN NEW;
END;
$many_to_many_duplicate$ LANGUAGE plpgsql;

CREATE SCHEMA vulnerability;

CREATE TYPE "public"."affected_range_type" AS ENUM (
    'git',
    'semver',
    'ecosystem'
    );

CREATE TYPE public.reference_type AS ENUM (
    'advisory',
    'article',
    'report',
    'fix',
    'git',
    'package',
    'web'
    );

CREATE TABLE vulnerability.vulnerability
(
    "id"                UUID PRIMARY KEY     DEFAULT public.gen_random_uuid(),
    "source"            text        NOT NULL,
    "source_id"         text        NOT NULL,
    "modified"          timestamptz NOT NULL DEFAULT (now()),
    "published"         timestamptz,
    "withdrawn"         timestamptz,
    "summary"           text,
    "details"           text,
    "database_specific" jsonb,
    "upstream_data"     jsonb,
    CONSTRAINT vulnerability_source_source_id_idx UNIQUE ("source", "source_id")
);

CREATE TABLE vulnerability.equivalent
(
    a UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    b UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    CONSTRAINT equivalent_a_b_idx UNIQUE (a, b)
);

-- For every insert into the vulnerability equivalent table, we replicate the pair of (a, b) as (b, a). This
-- allows for back references for other inserted vulnerabilities that need to know what is equivalent to it.
CREATE TRIGGER many_to_many_duplicate_vulnerability_equivalent
    BEFORE INSERT OR UPDATE
    ON vulnerability.equivalent
    FOR EACH ROW
    WHEN ( PG_TRIGGER_DEPTH() < 1 )
EXECUTE PROCEDURE many_to_many_duplicate();

CREATE TABLE vulnerability.severity
(
    id               UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    vulnerability_id UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    source           TEXT NOT NULL,
    type             TEXT NOT NULL,
    score            TEXT NOT NULL,
    CONSTRAINT severity_vulnerability_id_source_type_idx UNIQUE (vulnerability_id, source, type)
);

CREATE TABLE vulnerability.affected
(
    id                 UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    vulnerability_id   UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    package_id         UUID,
    ecosystem_specific jsonb,
    database_specific  jsonb,
    CONSTRAINT affected_vulnerability_id_package_id_idx UNIQUE (vulnerability_id, package_id)
);

CREATE TABLE vulnerability.affected_range_event
(
    "id"                UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    "affected_id"       UUID REFERENCES "vulnerability"."affected" ("id") ON DELETE CASCADE,
    "type"              affected_range_type NOT NULL,
    "event"             text NOT NULL,
    "version"           text NOT NULL,
    "database_specific" jsonb,
    CONSTRAINT affected_range_event_affected_id_type_event_version_idx UNIQUE ("affected_id", "type", "event", "version")
);

CREATE TABLE vulnerability.affected_version
(
    "id"                UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    "affected_id"       UUID REFERENCES "vulnerability"."affected" ("id") ON DELETE CASCADE,
    "version"           text                  NOT NULL,
    "database_specific" jsonb,
    CONSTRAINT affected_version_affected_id_type_version UNIQUE ("affected_id", "version")
);

CREATE TABLE vulnerability.reference
(
    id               UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    vulnerability_id UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    type             reference_type NOT NULL,
    url              TEXT           NOT NULL,
    CONSTRAINT reference_vulnerability_id_type_url_idx UNIQUE (vulnerability_id, type, url)
);

CREATE TABLE vulnerability.credit
(
    id               UUID PRIMARY KEY DEFAULT public.gen_random_uuid(),
    vulnerability_id UUID REFERENCES vulnerability.vulnerability (id) ON DELETE CASCADE,
    name             TEXT NOT NULL,
    contact          TEXT[],
    CONSTRAINT credit_vulnerability_id_name UNIQUE (vulnerability_id, name)
);
